
package sg.edu.nus.iss.cats.dataaccess.mysql;

import sg.edu.nus.iss.cats.dataaccess.*;
import sg.edu.nus.iss.cats.model.*;

import java.util.*;
import java.sql.*;


public class DepartmentDAOImpl implements DepartmentDAO {

    
    public Department findDepartment (String departmentId) throws DAOException{
        if (departmentId == null) {
            throw new DAOException ("Could not find Department: specified id is null");
        }

        String sql = "SELECT * FROM " + DBConstants.departmentsTableName + " WHERE "+           
                       DBConstants.d_departmentId  + "='" + departmentId + "'";
        Department[] results = retrieveDepartments (sql);
        if (results.length == 0) {
            return null;
        }
        return results[0];

    }

    public Department[] findAllDepartments () throws DAOException{

        String sql = "SELECT * FROM " + DBConstants.departmentsTableName;
        Department[] results = retrieveDepartments (sql);
        return results;
    }

    public void insertDepartment (Department department) throws DAOException {
    
            String insertSql = "INSERT INTO departments VALUES (?,?)";

            Connection conn = null;
            try {
                conn = DriverManager.getConnection(DBConstants.dbUrl,
                                      DBConstants.dbUsername,
                                     DBConstants.dbPassword);
                System.out.println("Created Connection");
                
                // Now add the new roles
                PreparedStatement pst = conn.prepareStatement(insertSql);

                System.out.println("Executing insert: " + insertSql);
                pst.setString(1, department.getDepartmentId());
                pst.setString(2, department.getManagerInCharge());
                pst.executeUpdate();
                System.out.println("Executed insert");
              
                pst.close();


            } catch (Exception e) {

                // If there is an error, we throw an exception.
                String error = "Error adding departments. Nested Exception is: " + e;
                System.out.println (error);
                throw new DAOException (error);

            } finally {
                try {
                    System.out.println("Closing connection");
                    conn.rollback();
                    conn.close();
                } catch (Exception e) {}
            }
        
    }

    public void updateDepartment (Department department) throws DAOUpdateException {
        String departmentId = department.getDepartmentId();
        if (departmentId == null) {
            throw new DAOUpdateException ("Could not find Department: specified id is null");
        }
        String sql = "SELECT * FROM " + DBConstants.departmentsTableName + " WHERE " + DBConstants.d_departmentId  + "='" + departmentId + "'";

        Connection conn = null;
        try {
            conn = DriverManager.getConnection(DBConstants.dbUrl,
                                 DBConstants.dbUsername,
                                DBConstants.dbPassword);
            System.out.println("Created Connection");
            Statement statement = conn.createStatement(
                ResultSet.TYPE_SCROLL_SENSITIVE,
                ResultSet.CONCUR_UPDATABLE);

            System.out.println("Executing query: " + sql);
            ResultSet rs = statement.executeQuery(sql);
            System.out.println("Executed query");

            rs.beforeFirst();
            do {
                rs.next();
                rs.updateString (DBConstants.d_departmentId, department.getDepartmentId());
                rs.updateString (DBConstants.d_managerInCharge, department.getManagerInCharge());
                rs.updateRow ();
            } while (!rs.isLast());
            rs.close();
            statement.close ();
            System.out.println("Modified objects");

        } catch (Exception e) {

            // If there is an error, we throw an exception.
            String error = "Error modifying object. Nested Exception is: " + e;
            System.out.println (error);
            throw new DAOUpdateException (error);

        } finally {
            try {
                System.out.println("Closing connection");
                conn.close();
            } catch (Exception e) {}
        }
    }

    public void deleteDepartment (String departmentId) throws DAOException {
         String deleteSql = "DELETE FROM " + DBConstants.departmentsTableName + 
                            " WHERE " + DBConstants.d_departmentId  + "='" + 
                            departmentId + "'";
         
          Connection conn = null;
            try {
                conn = DriverManager.getConnection(DBConstants.dbUrl,
                    DBConstants.dbUsername,
                    DBConstants.dbPassword);
                System.out.println("Created Connection");

                // Delete the new roles
                Statement st = conn.createStatement();
                System.out.println("Executing delete: " + deleteSql);
                int rows = st.executeUpdate(deleteSql);
                System.out.println("Executed delete"+rows);
                st.close();

            } catch (Exception e) {

                // If there is an error, we throw an exception.
                String error = "Error deleting departments. Nested Exception is: " + e;
                System.out.println (error);
                throw new DAOException (error);

            } finally {
                try {
                    System.out.println("Closing connection");
                    conn.rollback();
                    conn.close();
                } catch (Exception e) {}
            }
        
    }

 
    private Department[] retrieveDepartments (String sql) throws DAOException {
        Connection conn = null;
        
        Department[] result = null;
        try {
            conn = DriverManager.getConnection(DBConstants.dbUrl,
                                 DBConstants.dbUsername,
                                DBConstants.dbPassword);
            System.out.println("Created Connection");

            Statement statement = conn.createStatement();
            System.out.println("Executing query: " + sql);

            ResultSet rs = statement.executeQuery(sql);
            System.out.println("Executed query");

          
            System.out.println("Processing row");
            
            Vector v = new Vector();
        
           
             while (rs.next()) {
                Department obj = new Department ();
                obj.setDepartmentId     (rs.getString(DBConstants.d_departmentId));
                obj.setManagerInCharge (rs.getString(DBConstants.d_managerInCharge));
                v.add (obj);                
                result = new Department [v.size()];
                result = (Department[]) v.toArray (result);
            }


            rs.close();
            statement.close ();
            System.out.println("Retrieved Departments- number found: " + v.size());

         
        } catch (Exception e) {

            // If there is an error, we throw an exception.
            String error = "Error retrieving Departments. Nested Exception is: " + e;
            System.out.println (error);
            throw new DAOException (error);

        } finally {
            try {
                System.out.println("Closing connection");
                conn.close();
            } catch (Exception e) {}
        }

        // Convert the Vector to an array of Departments

        return (result);
    }
    
    
   


}